package orange.data;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

public class UNComtrade {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String content ="";
		Pattern p_option = Pattern.compile("<option.*?</option>"),p_value = Pattern.compile("value=\".*?\"");
		Hashtable<String, String> ht_country = new Hashtable<String, String>();
		try {
			content = FileReader("nationcode");
			Matcher m_option = p_option.matcher(content);
			while(m_option.find()){
//				System.out.println(m_option.group());
				String option = m_option.group();
				String code = option.replaceAll("<option value=\"", "").replaceAll("\">.*?</option>", "");
				String country = option.replaceAll("<option value=\".*?\">", "").replaceAll("</option>", "");
//				System.out.println(code+"----"+country);
				ht_country.put(code, country);
			}
			ht_country.put("0", "World");
			String[] data = FileReader("comtrade_trade_data_export").split("\n");
			JSONArray jarray = new JSONArray();
			for(String s:data){
				if(s.startsWith("2010")){
					
					String[] array = s.replace("\"", "").split(",");
					JSONObject bean = new JSONObject();
					String year = array[0];
					String Reporter = array[1];
					String Partner = array[3];
					String code = array[5];
					String Netweight = array[8];
					String value = array[9];
					bean.put("year", year);
					bean.put("Reporter", ht_country.get(Reporter));
					System.out.println(ht_country.get(Partner));
					bean.put("Partner", ht_country.get(Partner));
					bean.put("code", code);
					bean.put("Netweight", Netweight);
					bean.put("value", value);
					if(ht_country.get(Partner)!=null)
					jarray.put(bean);
				}
			}
			System.out.println(jarray.length());
			saveDocument(getSql(jarray), "data.txt");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private static String FileReader(String path) throws Exception {
		ArrayList<String> list = new ArrayList<String>();
		InputStream in = new FileInputStream(new File(path));
		BufferedReader r = new BufferedReader(
				new InputStreamReader(in, "UTF-8"));
		String line;
		StringBuffer sb = new StringBuffer();
		while ((line = r.readLine()) != null) {
//			list.add(line.trim().split(",")[1].replace("\"", ""));
			sb.append(line).append("\n");
		}
		r.close();
		in.close();
		return sb.toString();
	}
	// 将文件保存到本地
	public static  boolean saveDocument(String str,String filename) {		
		String path="/";
		try {
			File dirfile=new File(path);
			if(!dirfile.exists())
				dirfile.mkdir();//第一次创建目录
			
			File file = new File(path+filename); // 第二次创建这个文件就会把第一次的文件覆盖掉
			FileOutputStream fos = new FileOutputStream(file);
			fos.write(str.getBytes());
			fos.flush();
			fos.close();
			return true;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public static String getSql(JSONArray array ) throws Exception{
		StringBuffer sb = new StringBuffer();
//		JSONArray array = new JSONArray(content);
		String TABLE_NAME = "orang_w_jinchumaoyi";
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.WorldTrade.year+","+Params.WorldTrade.code+","+Params.WorldTrade.ec+","+Params.WorldTrade.ic+","+Params.WorldTrade.total+","+Params.WorldTrade.money+") VALUES ");		
		int length = array.length();
		String[] year = new String[length],code = new String[length],ec = new String[length],ic = new String[length],total = new String[length],money = new String[length];
		for(int i=0;i<length;i++){
			JSONObject bean = array.getJSONObject(i);
			year[i] = bean.getString("year");
			ec[i] = bean.getString("Reporter");
			System.out.println(bean.getString("Reporter")+i+bean.getString("Partner"));
			ic[i] = bean.getString("Partner");
			code[i] = bean.getString("code");
			total[i] = bean.getString("Netweight");
			money[i] = bean.getString("value");
		}
		
		String data = ParamsManager.getInsertString(year,code,ec,ic,total,money);
		sb.append(data);		
		return sb.toString();
	}
}
